# -*- coding: utf-8 -*-
import codecs
import json

import pymysql


# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
#定义爬取到的数据，如何处理：存储到json文件中
class Autodangdang2JsonFilePipeline(object):
    def __init__(self):
        self.file = codecs.open("D:/py/autopjt-dangdang2.json","wb",encoding="utf-8")
    
    #具体存储的实现
    def process_item(self, item, spider):
        item = dict(item)
        for j in range(0,len(item["name"])):
            name = item["name"][j]
            price = item["price"][j]
            comnum = item["comnum"][j]
            link = item["link"][j]
            goods = {"name":name,"price":price,"comnum":comnum,"link":link}
            
            i = json.dumps(dict(goods),ensure_ascii=False)
            line = i +"\n"
            #存储到json文件中
            self.file.write(line)
        return item
    
    def close_spider(self,spider):
        #关闭autopjt-dangdang.json文件流
        self.file.close()

#定义抓取到的数据，存储到Mysql数据库中
class Autodangdang2MysqlDBPipeline(object):
    def __init__(self):
        self.conn = pymysql.connect(host="127.0.0.1",user="root",passwd="root",db="autopjt",charset="utf8" )

    def process_item(self,item,spider):
        item = dict(item)
        
        sqlDatas = []
        for j in range(0,len(item["name"])):
            name = item["name"][j]
            price = item["price"][j]
            comnum = item["comnum"][j]
            link = item["link"][j]
            
            data = (name,price,comnum,link)
            sqlDatas.append(data)
        
        print("----------->总记录数：",len(sqlDatas))
        if len(sqlDatas)>0:
            sql = "INSERT INTO dangdang(name,price,comnum,link)VALUES(%s,%s,%s,%s)"
            #获取游标
            cur = self.conn.cursor()
            try:
                #批量插入到DB中
                cur.executemany(sql,sqlDatas)
                #数据库连接提交事务
                self.conn.commit()
            except Exception as e:
                print("事务回滚，出现异常：",e)
                self.conn.rollback()
                raise e
            finally:
                cur.close()  
                #插入到数据库中后，清空内存数据
                #sqlDatas.clear()  
                return item
        else:
            return item
        
    def close_spider(self,spider):
        #关闭数据库连接
        self.conn.close()
